﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using TrinityAPI.Base;
using TrinityAPI.Base.Models;

namespace TrinityAPI.Data.MySql
{
    public class DBHelper : DBBase, IDisposable
    {
        private void Init()
        {
            escapeFormat = "`{0}`";
            databaseType = DatabaseType.MYSQL;
            Operators["equalWhenNullAsEmpty"] = "ifnull({0},'')={1}";
            Operators["notEqualWhenNullAsEmpty"] = "ifnull({0},'')<>{1}";
        }

        public DBHelper(string connString) : base(connString)
        {
            Init();
        }

        public DBHelper(string connString, bool isTrans) : base(connString, isTrans)
        {
            Init();
        }

        public DBHelper(string connString, bool isTrans, IsolationLevel isolation) : base(connString, isTrans, isolation)
        {
            Init();
        }

        public DBHelper(string connString, bool isTrans, bool autoDispose) : base(connString, isTrans, autoDispose)
        {
            Init();
        }

        public DBHelper(string connString, bool isTrans, bool autoDispose, IsolationLevel isolation) : base(connString, isTrans, autoDispose, isolation)
        {
            Init();
        }

        public override string BuildPagingSql(string sql, string orderBy, int start, int end, bool isCount = true)
        {
            if (string.IsNullOrWhiteSpace(sql) || string.IsNullOrWhiteSpace(orderBy) || start > end)
                return string.Empty;

            List<string> orderByList = new List<string>();
            foreach (var item in orderBy.Split(','))
            {
                string orderByItem = item.Trim(' ');
                if (orderByList.IndexOf(".") > -1)
                    orderByItem = orderByItem.Split('.')[1];
                orderByList.Add("t." + orderByItem);
            }
            orderBy = string.Join(",", orderByList.ToArray());

            string countSql = isCount ? string.Format(";select count(0) as total from ({0}) t", sql) : string.Empty;
            return string.Format("select t.* from ({1}) t order by {0} limit {2},{3}{4}", orderBy, sql, start, end - start + 1, countSql);
        }

        public override DataSet Query(string cmdText, List<DBParameter> cmdParms = null, CommandType cmdType = CommandType.Text, int timeout = 30)
        {
            MySqlCommand cmd = PrepareCommand(cmdText, new MySqlDBParameters(cmdParms), cmdType, timeout);
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    if (this.autoDispose)
                        this.Dispose();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    this.Rollback();
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                }
                return ds;
            }
        }

        public override object ExecuteScalar(string cmdText, List<DBParameter> cmdParms = null, CommandType cmdType = CommandType.Text, int timeout = 30)
        {
            MySqlCommand cmd = PrepareCommand(cmdText, new MySqlDBParameters(cmdParms), cmdType, timeout);
            try
            {
                object obj = cmd.ExecuteScalar();
                if (this.autoDispose)
                    this.Dispose();
                return obj;
            }
            catch (Exception ex)
            {
                this.Rollback();
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
        }

        public override int ExecuteNonQuery(string cmdText, List<DBParameter> cmdParms = null, CommandType cmdType = CommandType.Text, int timeout = 30, bool hasOutput = false)
        {
            MySqlCommand cmd = PrepareCommand(cmdText, new MySqlDBParameters(cmdParms), cmdType, timeout);
            try
            {
                int val = cmd.ExecuteNonQuery();
                if (hasOutput)
                    this.GetOutputs(cmd.Parameters, cmdParms);
                if (this.autoDispose)
                    this.Dispose();
                return val;
            }
            catch (Exception ex)
            {
                this.Rollback();
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
        }

        private MySqlCommand PrepareCommand(string cmdText, List<MySqlParameter> cmdParms, CommandType cmdType = CommandType.Text, int timeout = 30)
        {
            if (this.connection == null)
                this.connection = new MySqlConnection(connectionString);

            var conn = (MySqlConnection)this.connection;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            cmd.CommandTimeout = timeout;

            if (this.isTransaction)
            {
                if (this.transaction == null)
                {
                    if (this.isolationLevel.HasValue)
                        this.transaction = conn.BeginTransaction(this.isolationLevel.Value);
                    else
                        this.transaction = conn.BeginTransaction();
                }
                cmd.Transaction = (MySqlTransaction)this.transaction;
            }

            if (cmdParms != null)
            {
                foreach (MySqlParameter parameter in cmdParms)
                {
                    try
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
            }
            return cmd;
        }
    }
}
